<?php
// $Id$

include_once('BaseModel.class.php');

class LookupModel extends BaseModel
{
    function levels ()
    {
        $query = "SELECT id,name,description FROM levels WHERE active = 1";
        return $this->query($query, true);
    }
    function prevyl ()
    {
        $query = "SELECT id,name,description FROM levels WHERE active = 1";
        return $this->query($query, true);
    }
    function school_year($active)
	{	
		$active = ($active) ? "status = OPEN" : "1";
        $query = "SELECT id,name,status FROM school_year WHERE active=1 AND $active ORDER BY name DESC";
        return $this->select_all($query);
    }
    function terms($level)
    {
        $query = "SELECT id,name FROM terms WHERE level_id = '$level'";
        return $this->query($query, true);
    }
    function items(){
       $query = "SELECT id,item FROM items";
        return $this->query($query,true);
    }
    function terms_details($term_id)
    {
        $query = "SELECT items.item , td.desc, amount,datedue, amount as `outstanding` FROM terms_details td
                  JOIN items ON items.id = item_id
                  WHERE term_id = '$term_id' AND td.active=1";
        return $this->query($query, true);
	}

	function other_charges($level,$cat_id=''){
		$cat_id = ($cat_id) ? " AND cat_id='$cat_id'" : "";
        $query = "SELECT name,price,id FROM othercharges WHERE level_id IN ('$level','') $cat_id ORDER BY name";
        return $this->query($query, true);
	}
	function other_charges_details($id){
	    $query = "SELECT id,name,`desc`,price FROM `othercharges` WHERE id IN ($id);";
        return $this->select_all($query);
	}
    function genders()
    {
       $query = "SELECT id,name,type FROM look_up WHERE active = 1 AND type='Gender'";
        return $this->query($query, true);
    }
    function religions()
    {
       $query = "SELECT id,name,type FROM look_up WHERE active = 1 AND type='Religion'";
     //   $query = "SELECT id,name,description FROM levels WHERE active = 1";
        return $this->query($query, true);
    }
    
    function nationalities()
    {
       $query = "SELECT id,concat(name,'-','(',code,')') as name,type,code FROM look_up WHERE active = 1 AND type='Nationality'";
     //   $query = "SELECT id,name,description FROM levels WHERE active = 1";
        return $this->query($query, true);
    }
    function schoolyear()
    {
       $query = "SELECT id, name FROM school_year WHERE active = 1";
     //   $query = "SELECT id,name,description FROM levels WHERE active = 1";
        return $this->query($query, true);
    }
    function status()
    {
       $query = "SELECT id,name,type FROM look_up WHERE active = 1 AND type='Status'";
     //   $query = "SELECT id,name,description FROM levels WHERE active = 1";
        return $this->query($query, true);
    }
    function en_status()
    {
       $query = "SELECT id,name,type FROM look_up WHERE active = 1 AND type='en_status'";
     //   $query = "SELECT id,name,description FROM levels WHERE active = 1";
        return $this->query($query, true);
    }
    function sumr_status()
    {
       $query = "SELECT id,name,type FROM look_up WHERE active = 1 AND type='sumr_status'";
     //   $query = "SELECT id,name,description FROM levels WHERE active = 1";
        return $this->query($query, true);
    }
    function credentials(){
        $query = "SELECT title,id FROM credentials ORDER BY title";
        return $this->query($query, true);
	}
	function credential_details($id){
	    $query = "SELECT title FROM `credentials` WHERE id='$id' LIMIT 1;";
        return $this->select_one($query);
	}
    function category(){
        $query = "SELECT title,id FROM categories WHERE active = 1 ORDER BY title";
        return $this->select_all($query);
	}

	function term_description(){
        $query = "SELECT * FROM look_up WHERE active = 1 AND type='term_description'";
        return $this->select_all($query);
	}
	function userlist(){
	$query = "SELECT login FROM auths WHERE active = 1 ORDER BY login";
        return $this->select_all($query);
	}
        function sections()
        {
          $query = "SELECT id,name,code FROM sections WHERE active = 1";
          return $this->query($query, true);
        }
    function privilege()
    {
       $query = "SELECT id,usertype_name,usertype_description FROM usertype WHERE active = 1";
        return $this->query($query, true);
    }
    function rooms()
    {
       $query = "SELECT id,room_no,description FROM rooms WHERE active = 1";
        return $this->query($query, true);
    }
    function advisers()
    {
       $query = "SELECT id,CONCAT(lname,', ',fname,' ',mname) as advisers FROM teachers WHERE active = 1";
        return $this->query($query, true);
    }

//Get all member of the group
    function members($gid)
    {
        $query = "SELECT m.id,concat(m.type, ' - ',m.stud_no,'-', m.firstname) as name FROM `members` m
                                                        JOIN group_members gm ON gm.member_id = m.id
                                                        WHERE group_id='$gid' AND m.active=1 ORDER by m.type, m.firstname";
        return $this->query($query, true);
    }
//Get avalable Contacts
    function members_list($gid)
    {
        $query = "SELECT concat(m.type, ' - ',m.stud_no,'-', m.firstname) as name,m.id FROM `members` m
                  WHERE id not in (SELECT member_id FROM group_members WHERE group_id='$gid') AND m.active = 1
                                                                ORDER BY m.type, m.firstname";
        return $this->query($query, true);
    }
    function contacts()
    {
       $query = "SELECT id,CONCAT(firstname,' ',middlename,' ',firstname)as name,msisdn FROM members WHERE active = 1";
     //   $query = "SELECT id,name,description FROM levels WHERE active = 1";
        return $this->query($query, true);
    }
    function user_list()
    {
       $query = "SELECT id,CONCAT(lastname,', ',firstname,' ',middlename) as user FROM auths WHERE active = 1";
        return $this->query($query, true);
    }
    function grouplist ()
    {
        $query = "SELECT id,name,description FROM groups WHERE active = 1 ORDER BY `name` ASC";
        return $this->query($query, true);
    }
    function get_grouplist()
    {
       $query = "SELECT id,modulegroup_name as name,description FROM user_modulegroups WHERE active = 1";
        return $this->query($query, true);
    }
    function privilege_level()
    {
       $query = "SELECT id,usertypelevel_name,usertypelevel_description FROM usertype_levels WHERE active = 1";
        return $this->query($query, true);
    }
    function folders()
    {
       $query = "SELECT id,name,type FROM folders WHERE active = 1";
        return $this->query($query, true);
    }
}
?>
